from PyQt4.QtCore import *
from PyQt4.QtGui import *
from PyQt4.QtSql import *
import Ui_billingSfsc
import billingSfscEdit
import master.utility as utility

class BillingSfsc(QDialog, Ui_billingSfsc.Ui_billingSfscDlg):
  def __init__(self, dbConn, dataGlob,  parent=None):
        super(BillingSfsc, self).__init__(parent)
        self.setupUi(self)       
        self.initMenu()
        self.db = dbConn
        self.dataGlob = dataGlob
        self.util = utility.Utility(self.db)
        self.per_le.setText(self.util.getServerToday().date().toString("MMyy"))
        self.mast_pgb.setVisible(False)
        
        self.mastModel = ModelMaster(self)
#        self.mastModel.setTable("trans_sfsc")
        self.mastModel.setEditStrategy(QSqlTableModel.OnManualSubmit)
        self.mastModel.setHeaderData(0, Qt.Horizontal, QVariant("Invoice No"))
        self.mastModel.setHeaderData(1, Qt.Horizontal, QVariant("Unit"))
        self.mastModel.setHeaderData(2, Qt.Horizontal, QVariant("Unit Name"))
        self.mastModel.setHeaderData(3, Qt.Horizontal, QVariant("Sqrm"))
        self.mastModel.setHeaderData(4, Qt.Horizontal, QVariant("SC Tarif"))
        self.mastModel.setHeaderData(5, Qt.Horizontal, QVariant("SF Tarif"))
        self.mastModel.setHeaderData(6, Qt.Horizontal, QVariant("Service Charge"))
        self.mastModel.setHeaderData(7, Qt.Horizontal, QVariant("Sinking Fund"))
        self.mastModel.setHeaderData(8, Qt.Horizontal, QVariant("VAT"))
        self.mastModel.setHeaderData(9, Qt.Horizontal, QVariant("Stamp Duty"))
        self.mastModel.setHeaderData(10, Qt.Horizontal, QVariant("Total SCSF"))
        
        self.master_tbv.setModel(self.mastModel)
        self.master_tbv.setItemDelegate(MasterDelegate(self))
        self.master_tbv.setSelectionMode(QTableView.SingleSelection)
        self.master_tbv.setSelectionBehavior(QTableView.SelectRows)        
        
        
  @pyqtSignature("")
  def on_per_le_editingFinished(self):
      if self.per_le.text() != "":
          self.per_cb.setCheckState(Qt.Checked)
      else:
          self.per_cb.setCheckState(Qt.Unchecked)
        
  @pyqtSignature("")
  def on_unit_le_editingFinished(self):
      if self.unit_le.text() != "":
          self.unit_cb.setCheckState(Qt.Checked)
      else:
          self.unit_cb.setCheckState(Qt.Unchecked)
        
  @pyqtSignature("int")
  def on_bill_cmb_currentIndexChanged(self, idx):
      self.bill_cb.setCheckState(Qt.Checked)
        
  def initMenu(self):
      self.retrieveAct = QAction(QIcon(":/retrieve.ico"), self.tr("Retrieve"), self)
      self.connect(self.retrieveAct, SIGNAL("triggered()"), self.retrieveData)
      self.sortAct = QAction(QIcon(":/sort.ico"), self.tr("Sort"), self)
      
      self.genAct = QAction(QIcon(":/add.ico"), self.tr("Process Billing"), self)
      self.connect(self.genAct, SIGNAL("triggered()"), self.generateData)
      self.postAct = QAction(QIcon(":/add.ico"), self.tr("Posting Billing"), self)
      self.connect(self.postAct, SIGNAL("triggered()"), self.postingData)
      
      self.addAct = QAction(QIcon(":/add.ico"), self.tr("Add"), self)
      self.connect(self.addAct, SIGNAL("triggered()"), self.addData)
      self.editAct = QAction(QIcon(":/edit.ico"), self.tr("Edit"), self)
      self.connect(self.editAct, SIGNAL("triggered()"), self.editData)
      self.delAct = QAction(QIcon(":/del.ico"), self.tr("Delete"), self)
      self.connect(self.delAct, SIGNAL("triggered()"), self.delData)
      self.printAct = QAction(QIcon("res/print.ico"), self.tr("Print Billing"), self)
      self.connect(self.printAct, SIGNAL("triggered()"), self.printData)
      self.printRptAct = QAction(QIcon("res/print.ico"), self.tr("Print Report"), self)
      self.connect(self.printRptAct, SIGNAL("triggered()"), self.printReport)
      
  def retrieveData(self):
      qStr = "select m.no_inv, m.kode_unit, m.nama_unit, format(m.luas_unit,2), format(m.tarif_sc,0), format(m.tarif_sf,0), format(m.sc,0), format(m.sf,0), format(m.pajak,0), format(m.materai_sc,0), format((m.sc+m.sf+m.pajak+m.materai_sc),0), m.kode_tagih_sc from trans_sfsc m "
      
      filt = ""
      urut = " order by m.no_inv"
      if self.per_cb.checkState() == Qt.Checked:
          filt = filt + " m.no_inv like '"+self.per_le.text()+"%' and "
      if self.unit_cb.checkState() == Qt.Checked:
          filt = filt + " m.kode_unit like '"+self.unit_le.text()+"%' and "
      if self.bill_cb.checkState() == Qt.Checked:
          if self.bill_cmb.currentIndex() == 1:
              filt = filt + " m.kode_tagih_sc = 'CBP10001' and "
          elif self.bill_cmb.currentIndex() == 2:
              filt = filt + " m.kode_tagih_sc <> 'CBP10001' and "
      if filt != "":
          filt = " where "+filt.left(len(filt)-4)
          
      qStr = qStr + filt + urut
      print qStr
      query = QSqlQuery(qStr)
      self.mastModel.setQuery(query)
      self.master_tbv.setColumnWidth(0, 70)
      self.master_tbv.setColumnWidth(1, 85)
      self.master_tbv.setColumnWidth(2, 200)
      self.master_tbv.setColumnWidth(3, 70)
      self.master_tbv.setColumnWidth(4, 60)
      self.master_tbv.setColumnWidth(5, 50)
      self.master_tbv.setColumnWidth(6, 120)
      self.master_tbv.setColumnWidth(7, 120)
      self.master_tbv.setColumnWidth(8, 120)
      self.master_tbv.setColumnWidth(9, 100)
      self.master_tbv.setColumnWidth(10, 200)
      self.master_tbv.setColumnHidden(11, True)
      self.master_tbv.resizeRowsToContents()
      
  def generateData(self):
      hari = self.util.getServerToday().date()
      per = hari.toString("MMyy")
      hari_bln_lalu = QDate(hari.year(),  hari.month(),  1)
      hari_bln_lalu = hari_bln_lalu.addDays(-1)
      per_lalu = hari_bln_lalu.toString("MMyy")
      
      query = QSqlQuery()
      query.exec_("select count(*) from trans_sfsc where periode = '"+per+"'")
      query.next()
      if query.value(0).toInt()[0] < 1:
          pesan = QMessageBox.question(self, "Billing SFSC", "Invoice on '"+per+"' has been created. Abort?", QMessageBox.Yes|QMessageBox.No)
          if pesan == QMessageBox.Yes:
              return
      
      pesan = QMessageBox.question(self, "Billing SFSC", "Invoice on '"+per+"' will be created. Continue?", QMessageBox.Yes|QMessageBox.No)
      if pesan == QMessageBox.No:
          return
      
      query.exec_("select count(*) from base_unit_hist where date_format(periode,'%m%y') = '"+per+"'")
      query.next()
      if query.value(0).toInt()[0] < 1:
          QMessageBox.warning(self, "Billing SFSC", "Process base unit before generate!")
          return
          
      query.exec_("select awal_sc, akhir_sc, cetak_sc, due_date_sc from master_setup")
      query.next()
      tgl_awal = query.value(0).toDate()
      tgl_akhir = query.value(1).toDate()
      tgl_cetak = query.value(2).toDate()
      tgl_tempo = query.value(3).toDate()
      
      if per != tgl_cetak.toString("MMyy"):
          QMessageBox.warning(self, "Billing SFSC", "Please check billing periode! Print date on sistem setup is '"+tgl_cetak.toString("dd-MM-yyyy")+"'")
          return
          
      self.master_tbv.setEnabled(False)
      self.meter_pgb.setVisible(True)
# ============== START PROSES ==============          
      sfscModel = QSqlTableModel(self)
      sfscModel.setTable("trans_sfsc")
      sfscModel.setEditStrategy(QSqlTableModel.OnManualSubmit)
      sfscModel.setFilter("periode = '"+per+"'")
      sfscModel.select()
      
      detModel = QSqlTableModel(self)
      detModel.setTable("trans_sfsc_detail")
      detModel.setEditStrategy(QSqlTableModel.OnManualSubmit)
      detModel.setFilter("no_bill like '"+per+"%'")
      detModel.select()
      
      tarifModel = QSqlQueryModel(self)
      qStr = "select kode_tarif, tarif_satuan from mater_tarif_detail where status = 'A' order by kode_tarif"
      tarifModel.setQuery(qStr, self.db)
      
      areaModel = QSqlQueryModel(self)
      
      unitModel = QSqlQueryModel(self)
      qStr = "select u.kode_unit, u.unit, u.nama_unit, u.luas_unit, u.tenant, u.owner, u.stat_bill_sc, u.stat_bill_sf, u.sc_bill_to, u.buka, u.status_join, u.kode_tu, t.keterangan, "\
      "(select b.kode_tarif from base_unit_det_bill b where b.kode_unit = u.kode_unit and tipe_tagih = 'SC') as tarif_sc, "\
      "(select b.kode_tarif from base_unit_det_bill b where b.kode_unit = u.kode_unit and tipe_tagih = 'SF') as tarif_sf "\
      "from base_unit u "\
      "left outer join master_type_unit t on t.kode_tu = u.kode_tu "
      filt = " where (u.stat_bill_sc = 'Y' or u.stat_bill_sf = 'Y') and status_join <> 'J' "
      urut = " order by u.unit "
      qStr = qStr + filt + urut
      unitModel.setQuery(qStr, self.db)
      jum_brs = unitModel.rowCount()
      
      kode_dev = "CBP10001"
      urut_m = 0
      urut_d = 0
      query.exec_("select convert(max(right(no_inv,4)), signed) from trans_sfsc where left(no_inv,4) = '"+per+"'")
      while query.next():
          urut_m = query.value(0).toInt()[0]
      query.exec_("select convert(max(right(no_bill,4)), signed) from trans_sfsc_detail where left(no_bill,4) = '"+per+"'")
      while query.next():
          urut_d = query.value(0).toInt()[0]
          
      self.db.transaction()
      for i in range(jum_brs):
          luas = 0
          luas_join = 0
          tarif_sc = 0
          tarif_sf = 0
          sc_rp = 0
          sf_rp = 0
          total_rp = 0
          met = 0
          denda_rp = 0
          prev_rp = 0
          last_pay = 0
          sfscModel.insertRow(i)
          
          urut_m += 1
          no_inv = per + "-" +"0"*(4 - len(str(urut_m)))+str(urut_m)
          sfscModel.setData(sfscModel.index(i, 0), QVariant(no_inv))
          sfscModel.setData(sfscModel.index(i, 1), QVariant(per))
          
          stat_join = unitModel.record(i).value("status_join").toString()
          unit_asli = unitModel.record(i).value("unit").toString()
          kode_unit = unitModel.record(i).value("kode_unit").toString()
          tagih_ke = unitModel.record(i).value("sc_bill_to").toString()
          luas = unitModel.record(i).value("luas_unit").toDouble()[0]
          
          if tagih_ke == '3' :
              unit = unit_asli + "-D"
              tagih = kode_dev
          else:
              if stat_join == "N":
                  unit = unit_asli + "-S"
              else:
                  unit = unit_asli + "-G"
                  query.exec_("select sum(luas_unit) from base_unit where kode_unit in ( select unit_join from base_unit_detail where kode_unit = '"+kode_unit+"' ) ")
                  query.next()
                  luas += query.value(0).toDouble()[0]
              if tagih_ke == "1":
                  tagih = unitModel.record(i).value("owner").toString()
              else:
                  tagih = unitModel.record(i).value("tenant").toString()
              if tagih == kode_dev:
                  unit = unit_asli + "-D"
                  
          sfscModel.setData(sfscModel.index(i, 2), QVariant(unit))
          sfscModel.setData(sfscModel.index(i, 3), QVariant(luas))
          sfscModel.setData(sfscModel.index(i, 41), QVariant(tagih))
          
          #---------------------------------- Service Charge ---------------------------------
          tarif = unitModel.record(i).value("tarif_sc")
          brs_tarif = self.tarifModel.match(self.tarifModel.index(0, 0), 0, tarif,1, Qt.MatchWrap )
          tarif_sc = 0
          if len(brs_tarif) != 0:
              #TODO: please cek hasil tarif sc ini, kayaknya salah
              tarif_sc = brs_tarif[0].row()
          sfscModel.setData(sfscModel.index(i, 4), QVariant(tarif_sc))
          sc_rp = tarif_sc*luas
          sfscModel.setData(sfscModel.index(i, 21), QVariant(sc_rp))
          sfscModel.setData(sfscModel.index(i, 15), QVariant(sc_rp))
          
          #---------------------------------- Sinking Fund ---------------------------------
          tarif = unitModel.record(i).value("tarif_sf")
          brs_tarif = self.tarifModel.match(self.tarifModel.index(0, 0), 0, tarif,1, Qt.MatchWrap)
          tarif_sf = 0
          if len(brs_tarif) != 0:
              tarif_sf = brs_tarif[0].row()
          sfscModel.setData(sfscModel.index(i, 5), QVariant(tarif_sf))
          sf_rp = tarif_sf*luas
          sfscModel.setData(sfscModel.index(i, 22), QVariant(sf_rp))
          sfscModel.setData(sfscModel.index(i, 14), QVariant(sf_rp))
          
          #---------------------------------- Meterai ---------------------------------
          total_rp = sc_rp + sf_rp
          if tagih != kode_dev:
              if total_rp > 1000000:
                  met = 6000
              elif total_rp > 250000:
                  met = 3000
          sfscModel.setData(sfscModel.index(i, 20), QVariant(met))
          sfscModel.setData(sfscModel.index(i, 43), QVariant(met))
          
          #---------------------------------- Denda ---------------------------------
          if tagih != kode_dev:
              qStr = "select 0.03*(sum(s.sc+s.sf+s.materai_sc)) from trans_sfsc s left outer join trans_payment_sfsc p on p.no_inv = s.no_inv where left(s.kode_unit,8) = '"+unit_asli+"' and s.kode_tagih_sc <> '"+kode_dev+"' and s.status_lunas = 'N' and p.no_payment is null"
              query.exec_(qStr)
              while query.next():
                  denda_rp = query.value(0).toDouble()[0]
          sfscModel.setData(sfscModel.index(i, 19), QVariant(denda_rp))
          
          #---------------------------------- Prev Balance ---------------------------------
          if tagih != kode_dev:
              qStr = "select sc+sf+materai_sc+prev_balance+pajak-tot_last_payment from trans_sfsc where left(kode_unit,8) = '"+unit_asli+"' and left(no_inv,4) = '"+per_lalu+"'"
              query.exec_(qStr)
              while query.next():
                  prev_rp = query.value(0).toDouble()[0]
          sfscModel.setData(sfscModel.index(i, 41), QVariant(prev_rp))
          
          #---------------------------------- Last Payment ---------------------------------
          if tagih != kode_dev:
              qStr = "select sum(total_payment) from trans_payment_sfsc where left(unit,8) = '"+unit_asli+"' and status_bill = 'N'"
              query.exec_(qStr)
              while query.next():
                  last_pay = query.value(0).toDouble()[0]
                  if abs(prev_rp - last_pay) < 1000:
                      last_pay = prev_rp
          sfscModel.setData(sfscModel.index(i, 42), QVariant(last_pay))
          
          #---------------------------------- Other ---------------------------------
          sfscModel.setData(sfscModel.index(i, 8), QVariant(tgl_awal))
          sfscModel.setData(sfscModel.index(i, 9), QVariant(tgl_akhir))
          sfscModel.setData(sfscModel.index(i, 10), QVariant(tgl_cetak))
          sfscModel.setData(sfscModel.index(i, 11), QVariant(tgl_tempo))
          sfscModel.setData(sfscModel.index(i, 19), QVariant("N"))
          sfscModel.setData(sfscModel.index(i, 20), QVariant("N"))
          sfscModel.setData(sfscModel.index(i, 23), QVariant("N"))
          sfscModel.setData(sfscModel.index(i, 24), QVariant("N"))
          sfscModel.setData(sfscModel.index(i, 30), QVariant("N"))
          sfscModel.setData(sfscModel.index(i, 40), QVariant("N"))
          sfscModel.setData(sfscModel.index(i, 27), QVariant("Nama"))
          sfscModel.setData(sfscModel.index(i, 28), QVariant("validasi"))
          sfscModel.setData(sfscModel.index(i, 29), QVariant("prepare"))
          sfscModel.setData(sfscModel.index(i, 47), unitModel.record(i).value("nama_unit"))
          
          #---------------------------------- Detail SFSC ---------------------------------
          tipe_unit = unitModel.record(i).value("keterangan").toString()
          if tipe_unit.left(3) == "EXT":
              qStr = "select kode_unit, lantai, luas, harga_sc, harga_sf from base_unit_det_unit where kode_unit = '"+kode_unit+"'"
              areaModel.setQuery(qStr)
              jum_det = areaModel.rowCount()
              for j in range(jum_det):
                  detModel.inserRow(j)
                  urut_d += 1
                  no_bill = per + "-" +"0"*(4 - len(str(urut_d)))+str(urut_d)
                  detModel.setData(detModel.index(i, 0), QVariant(no_bill))
                  detModel.setData(detModel.index(i, 1), QVariant(no_inv))
                  lantai = areaModel.record(j).value("lantai").toString()
                  detModel.setData(detModel.index(i, 2), QVariant(lantai))
                  luas_det = 0
                  if stat_join == "G":
                      query.exec_("select sum(luas) from base_unit_det_unit where lantai = '"+lantai+"' and kode_unit in ( select unit_join from base_unit_detail where kode_unit = '"+kode_unit+"' )")
                  luas_det += areaModel.record(j).value("luas").toDouble()[0]
                  detModel.setData(detModel.index(i, 3), QVariant(luas_det))
                  harga_sc_det = areaModel.record(j).value("harga_sc").toDouble()[0]
                  harga_sf_det = areaModel.record(j).value("harga_sf").toDouble()[0]
                  detModel.setData(detModel.index(i, 4), QVariant(harga_sc_det))
                  detModel.setData(detModel.index(i, 5), QVariant(harga_sf_det))
                  detModel.setData(detModel.index(i, 6), QVariant(luas_det*harga_sc_det))
                  detModel.setData(detModel.index(i, 7), QVariant(luas_det*harga_sf_det))
          self.mast_pgb.setValue(i)
          
# ============== END PROSES ==============          
      sfscModel.submitAll()
      self.db.commit()
      self.master_tbv.setEnabled(True)
      self.meter_pgb.setVisible(False)
      
  def postingData(self):
      query = QSqlQuery()
#      query.exec_("select ar_listrik,  rv_listrik, ar_air, rv_air, ar_gas, rv_gas, rv_matc, ar_matc, ket_arrv_materai, ket_arrv_denda, ket_arrv_listrik, ket_arrv_air, ket_arrv_gas, jt_listrik, jt_air, jt_gas, jt_materai, ar_sc, rv_sc,  ")      
      query.exec_("select rv_matc, ar_matc, ket_arrv_materai, ket_arrv_denda, jt_list, jt_materai, ar_sc, rv_sc, ar_sf, rv_sf, ket_arrv_sc, ket_arrv_sf, jt_sc, jt_sf, rv_sc2, rv_sc3 from master_setup")
      query.next()
      rv_mat = query.value(0).toString()
      acc_mat = query.value(1).toString()
      ket_mat = query.value(2).toString()
      ket_denda = query.value(3).toString()
      jt_lis = query.value(4).toString()
      jt_mat = query.value(5).toString()
      acc_sc = query.value(6).toString()
      rv_sc = query.value(7).toString()
      acc_sf = query.value(8).toString()
      rv_sf = query.value().toString()
      ket_sc = query.value().toString()
      ket_sf = query.value().toString()
      jt_sc = query.value().toString()
      jt_sf = query.value().toString()
      rv_sc2 = query.value().toString()
      rv_sc3 = query.value().toString()
      
      periode = self.mastModel.record(1).value("no_inv").toString().left(4)
      query.exec_("select count(*) from trans_sfsc where posted_sc = 'Y' and periode = '"+periode+"'")
      query.next()
      if query.value(0).toInt()[0] > 0:
          QMessageBox.warning(self,"Billing SFSC", "This data has been posted. Cannot post again!")
          return
#===============      POSTING SC
      kode = "JV"
      hari = self.util.getServerToday().date()
      jper = hari.toString("MMyyyy")      
      perkode = hari.toString("yyyy-MM")
      username = self.dataGlob["nama"]
      
      #DEV
      noAkhir = self.util.getNewId(kode, jper, "trans jurnal")
      no_jurnal = kode+"-"+jper+"-"+"0"*(4 - len(str(noAkhir)))+str(noAkhir)
      desk = ket_sc + " " + self.util.getServerToday().date().toString("MMMM yyyy") + " DEVELOPER "
      sc_dev = 0
      query.exec_("select sum(s.sc) from trans_sfsc s left outer join master_pemilik_penyewa p on p.kode_pemilik_penyewa = s.kode_tagih_sc where p.kode_tt = 'DEV' and left(s.no_inv,4) = '"+periode+"'")
      while query.next():
          sc_dev = query.value(0).toDouble()[0]
      if sc_dev > 0:
          query.prepare("insert into trans_jurnal(tgl_jurnal, no_jurnal, desk_jurnal, kode_user, tgl_entry, kode_periode, jurnal_type) values (:tgl, :no, :desk, :user, :tgl, :per, :tipe)")
          query.bindValue(":tgl", QVariant(hari))
          query.bindValue(":no", QVariant(no_jurnal))
          query.bindValue(":desk", QVariant(desk))
          query.bindValue(":user", QVariant(username))
          query.bindValue(":per", QVariant(perkode))
          query.bindValue(":tipe", QVariant(jt_lis))
          query.exec_()
          
          query.prepare("insert into trans_jurnal_detail(kode_perkiraan, no_jurnal, debit, kredit, urut) values (:kode, :no, :deb, :kre, :urut)")
          query.bindValue(":kode", QVariant(acc_sc))
          query.bindValue(":no", QVariant(no_jurnal))
          query.bindValue(":deb", QVariant(sc_dev))
          query.bindValue(":kre", QVariant(0))
          query.bindValue(":urut", QVariant(1))
          query.exec_()
          
          query.bindValue(":kode", QVariant(rv_sc3))
          query.bindValue(":no", QVariant(no_jurnal))
          query.bindValue(":deb", QVariant(0))
          query.bindValue(":kre", QVariant(sc_dev))
          query.bindValue(":urut", QVariant(2))
          query.exec_()
          
      #BIG TENANT
      noAkhir = self.util.getNewId(kode, jper, "trans jurnal")
      no_jurnal = kode+"-"+jper+"-"+"0"*(4 - len(str(noAkhir)))+str(noAkhir)
      desk = ket_sc + " " + self.util.getServerToday().date().toString("MMMM yyyy") + " BIG TENANT "
      sc_bt = 0
      query.exec_("select sum(s.sc) from trans_sfsc s left outer join master_pemilik_penyewa p on p.kode_pemilik_penyewa = s.kode_tagih_sc where p.kode_tt = 'BT' and left(s.no_inv,4) = '"+periode+"'")
      while query.next():
          sc_bt = query.value(0).toDouble()[0]
      if sc_bt > 0:
          query.prepare("insert into trans_jurnal(tgl_jurnal, no_jurnal, desk_jurnal, kode_user, tgl_entry, kode_periode, jurnal_type) values (:tgl, :no, :desk, :user, :tgl, :per, :tipe)")
          query.bindValue(":tgl", QVariant(hari))
          query.bindValue(":no", QVariant(no_jurnal))
          query.bindValue(":desk", QVariant(desk))
          query.bindValue(":user", QVariant(username))
          query.bindValue(":per", QVariant(perkode))
          query.bindValue(":tipe", QVariant(jt_lis))
          query.exec_()
          
          query.prepare("insert into trans_jurnal_detail(kode_perkiraan, no_jurnal, debit, kredit, urut) values (:kode, :no, :deb, :kre, :urut)")
          query.bindValue(":kode", QVariant(acc_sc))
          query.bindValue(":no", QVariant(no_jurnal))
          query.bindValue(":deb", QVariant(sc_bt))
          query.bindValue(":kre", QVariant(0))
          query.bindValue(":urut", QVariant(1))
          query.exec_()
          
          query.bindValue(":kode", QVariant(rv_sc2))
          query.bindValue(":no", QVariant(no_jurnal))
          query.bindValue(":deb", QVariant(0))
          query.bindValue(":kre", QVariant(sc_bt))
          query.bindValue(":urut", QVariant(2))
          query.exec_()
          
      #TENANT
      noAkhir = self.util.getNewId(kode, jper, "trans jurnal")
      no_jurnal = kode+"-"+jper+"-"+"0"*(4 - len(str(noAkhir)))+str(noAkhir)
      desk = ket_sc + " " + self.util.getServerToday().date().toString("MMMM yyyy") + " TENANT "
      sc_tu = 0
      query.exec_("select sum(s.sc) from trans_sfsc s left outer join master_pemilik_penyewa p on p.kode_pemilik_penyewa = s.kode_tagih_sc where p.kode_tt = 'TU' and left(s.no_inv,4) = '"+periode+"'")
      while query.next():
          sc_tu = query.value(0).toDouble()[0]
      if sc_tu > 0:
          query.prepare("insert into trans_jurnal(tgl_jurnal, no_jurnal, desk_jurnal, kode_user, tgl_entry, kode_periode, jurnal_type) values (:tgl, :no, :desk, :user, :tgl, :per, :tipe)")
          query.bindValue(":tgl", QVariant(hari))
          query.bindValue(":no", QVariant(no_jurnal))
          query.bindValue(":desk", QVariant(desk))
          query.bindValue(":user", QVariant(username))
          query.bindValue(":per", QVariant(perkode))
          query.bindValue(":tipe", QVariant(jt_lis))
          query.exec_()
          
          query.prepare("insert into trans_jurnal_detail(kode_perkiraan, no_jurnal, debit, kredit, urut) values (:kode, :no, :deb, :kre, :urut)")
          query.bindValue(":kode", QVariant(acc_sc))
          query.bindValue(":no", QVariant(no_jurnal))
          query.bindValue(":deb", QVariant(sc_tu))
          query.bindValue(":kre", QVariant(0))
          query.bindValue(":urut", QVariant(1))
          query.exec_()
          
          query.bindValue(":kode", QVariant(rv_sc3))
          query.bindValue(":no", QVariant(no_jurnal))
          query.bindValue(":deb", QVariant(0))
          query.bindValue(":kre", QVariant(sc_tu))
          query.bindValue(":urut", QVariant(2))
          query.exec_()
          
#===============      POSTING SF
      noAkhir = self.util.getNewId(kode, jper, "trans jurnal")
      no_jurnal = kode+"-"+jper+"-"+"0"*(4 - len(str(noAkhir)))+str(noAkhir)
      desk = ket_sf + " " + self.util.getServerToday().date().toString("MMMM yyyy") 
      sf = 0
      query.exec_("select sum(sc) from trans_sfsc where left(no_inv,4) = '"+periode+"'")
      while query.next():
          sf = query.value(0).toDouble()[0]
      if sf > 0:
          query.prepare("insert into trans_jurnal(tgl_jurnal, no_jurnal, desk_jurnal, kode_user, tgl_entry, kode_periode, jurnal_type) values (:tgl, :no, :desk, :user, :tgl, :per, :tipe)")
          query.bindValue(":tgl", QVariant(hari))
          query.bindValue(":no", QVariant(no_jurnal))
          query.bindValue(":desk", QVariant(desk))
          query.bindValue(":user", QVariant(username))
          query.bindValue(":per", QVariant(perkode))
          query.bindValue(":tipe", QVariant(jt_lis))
          query.exec_()
          
          query.prepare("insert into trans_jurnal_detail(kode_perkiraan, no_jurnal, debit, kredit, urut) values (:kode, :no, :deb, :kre, :urut)")
          query.bindValue(":kode", QVariant(acc_sf))
          query.bindValue(":no", QVariant(no_jurnal))
          query.bindValue(":deb", QVariant(sf))
          query.bindValue(":kre", QVariant(0))
          query.bindValue(":urut", QVariant(1))
          query.exec_()
          
          query.bindValue(":kode", QVariant(rv_sf))
          query.bindValue(":no", QVariant(no_jurnal))
          query.bindValue(":deb", QVariant(0))
          query.bindValue(":kre", QVariant(sf))
          query.bindValue(":urut", QVariant(2))
          query.exec_()
          
      
#===============      POSTING MATERAI ===============
      noAkhir = self.util.getNewId(kode, jper, "trans jurnal")
      no_jurnal = kode+"-"+jper+"-"+"0"*(4 - len(str(noAkhir)))+str(noAkhir)
      desk = ket_mat + " SFSC Period " + self.util.getServerToday().date().toString("MMMM yyyy") 
      mat = 0
      query.exec_("select sum(materai_sc + materai_sf) from trans_sfsc where left(no_inv,4) = '"+periode+"'")
      while query.next():
          mat = query.value(0).toDouble()[0]
      if mat > 0:
          query.prepare("insert into trans_jurnal(tgl_jurnal, no_jurnal, desk_jurnal, kode_user, tgl_entry, kode_periode, jurnal_type) values (:tgl, :no, :desk, :user, :tgl, :per, :tipe)")
          query.bindValue(":tgl", QVariant(hari))
          query.bindValue(":no", QVariant(no_jurnal))
          query.bindValue(":desk", QVariant(desk))
          query.bindValue(":user", QVariant(username))
          query.bindValue(":per", QVariant(perkode))
          query.bindValue(":tipe", QVariant(jt_lis))
          query.exec_()
          
          query.prepare("insert into trans_jurnal_detail(kode_perkiraan, no_jurnal, debit, kredit, urut) values (:kode, :no, :deb, :kre, :urut)")
          query.bindValue(":kode", QVariant(acc_mat))
          query.bindValue(":no", QVariant(no_jurnal))
          query.bindValue(":deb", QVariant(mat))
          query.bindValue(":kre", QVariant(0))
          query.bindValue(":urut", QVariant(1))
          query.exec_()
          
          query.bindValue(":kode", QVariant(rv_mat))
          query.bindValue(":no", QVariant(no_jurnal))
          query.bindValue(":deb", QVariant(0))
          query.bindValue(":kre", QVariant(mat))
          query.bindValue(":urut", QVariant(2))
          query.exec_()
          
      query.exec_("update trans_sfsc set posted_sc = 'Y' where left(no_inv,4) = '"+periode+"'")
      QMessageBox.information(self,  "Billing SFSC", "Service charge has been posted.")
      
      
  def addData(self):
      form = billingSfscEdit.BillingSfscEdit(self.db, "add",  self.dataGlob, self)
      form.show()

  def editData(self):
      index = self.master_tbv.currentIndex()
      if index.isValid():
          brs = self.mastModel.record(index.row())
          id = brs.value("no_inv").toString()
          form = billingSfscEdit.BillingSfscEdit(self.db, id, self.dataGlob, self)
          form.show()

  def delData(self):
      index = self.master_tbv.currentIndex()
#      if index.isValid():
#          brs = self.mastModel.record(index.row())
#          id = brs.value("kode_bu").toString()
#          nama = brs.value("nama_bu").toString()
#          if QMessageBox.question(self, "Business Unit","Delete Data : "+nama+"?", QMessageBox.Yes|QMessageBox.No) == QMessageBox.Yes:
#              qMast = QSqlQuery(self.db)
#              self.db.transaction()
#              qMast.exec_("delete from master_bisnis_unit where kode_bu = '"+id+"'")
#              self.db.commit()
#              self.retrieveData()

  def sortData(self):
       tes = ""

  def printData(self):
       tes = ""

  def printReport(self):
       tes = ""

  def contextMenuEvent(self, event):
        self.menu = QMenu(self)
        self.menu.addAction(self.retrieveAct)
        self.menu.addAction(self.sortAct)
        self.menu.addSeparator()
        self.menu.addAction(self.genAct)
        self.menu.addAction(self.postAct)
        self.menu.addSeparator()
        self.menu.addAction(self.addAct)
        self.menu.addAction(self.editAct)
        self.menu.addSeparator()
        self.menu.addAction(self.delAct)
        self.menu.addSeparator()
        self.menu.addAction(self.printAct)
        self.menu.addAction(self.printRptAct)
        self.menu.exec_(event.globalPos())

class ModelMaster(QSqlTableModel):
    def __init__(self, parent=None):
        super(ModelMaster, self).__init__(parent)
        
    def flags(self, index):
        flags = Qt.ItemIsSelectable|Qt.ItemIsEnabled
#        flags = QSqlTableModel.flags(self, index)        
#        if index.column() < 5:
#            flags = Qt.ItemIsSelectable|Qt.ItemIsEnabled
        return flags
        
class MasterDelegate(QItemDelegate):
    def __init__(self, parent=None):
        super(MasterDelegate, self).__init__(parent)
        
    def paint(self, painter, option, index):
        myoption = QStyleOptionViewItem(option)
        if index.column() > 2:
            myoption.displayAlignment |= Qt.AlignRight|Qt.AlignVCenter
        QItemDelegate.paint(self, painter, myoption, index)        
        
        
if __name__ == "__main__":
    import sys
    app = QApplication(sys.argv)
    form = BillingSfsc()
    form.show()
    app.exec_()
